--ORACLE

--SELECT SCHEMA  
SELECT OWNER AS NAME, sysdate AS CRTM, 1 AS INUSE
FROM sys.all_tables GROUP BY OWNER

--SELECT TABLE   
SELECT TRIM(OWNER) || '.' || TRIM(TABLE_NAME) AS NAME, 
	LAST_ANALYZED AS CRTM,
	1 AS INUSE
FROM sys.all_tables

--SELECT TRIGGER 
SELECT TRIM(OWNER) || '.' || TRIM(TRIGGER_NAME) AS NAME, 
	TRIM(TABLE_OWNER) || '.' || TRIM(TABLE_NAME) AS OWTB,
	TRIGGER_BODY AS SQL,
	sysdate AS CRTM,
	CASE STATUS WHEN 'ENABLED' THEN 1 ELSE 0 END AS INUSE
FROM sys.all_triggers where OWNER LIKE 'DITS'

--SELECT VIEW
SELECT TRIM(OWNER) || '.' || TRIM(VIEW_NAME) AS  OWTB,
	TEXT AS SQL,
	sysdate AS CRTM,
	1 AS INUSE
FROM sys.all_views

--SELECT VALIDATE  
SELECT user FROM sys.dual

--DEPLOY TRIGGER DELETE
CREATE TRIGGER DITS.DATA_MONITOR_DTG
            AFTER DELETE ON DITS.DATA_MONITOR_TB 
            FOR EACH ROW  
BEGIN 
	INSERT INTO DITS.DATA_MONITOR_LOG(SID, NSM, CLTB, OPT, CLID, INUSE) 
		VALUES(DITS.DATA_MONITOR_LOG_SEQ.nextval, 'DITS.NSM', 'DITS.DATA_MONITOR_TB', 3, TRIM(CAST(:OLD.DATA_ID AS CHAR(128))), 1); 
END;

--UNDEPLOY TRIGGER DELETE
DROP TRIGGER DITS.DATA_MONITOR_DTG

--DEPLOY TRIGGER INSERT
CREATE TRIGGER DITS.DATA_MONITOR_ITG
			AFTER INSERT ON DITS.DATA_MONITOR_TB
            FOR EACH ROW
BEGIN
	INSERT INTO DITS.DATA_MONITOR_LOG(SID, NSM, CLTB, OPT, CLID, INUSE) 
		VALUES(DITS.DATA_MONITOR_LOG_SEQ.nextval, 'DITS.NSM', 'DITS.DATA_MONITOR_TB', 1, TRIM(CAST(:NEW.DATA_ID AS CHAR(128))), 1);
END;

--UNDEPLOY TRIGGER INSERT
DROP TRIGGER DITS.DATA_MONITOR_ITG


--DEPLOY TRIGGER UPDATE
CREATE TRIGGER DITS.DATA_MONITOR_UTG
            AFTER UPDATE ON DITS.DATA_MONITOR_TB
            FOR EACH ROW
BEGIN
	IF :OLD.DATA_ID = :NEW.DATA_ID THEN
		INSERT INTO DITS.DATA_MONITOR_LOG(SID, NSM, CLTB, OPT, CLID, INUSE)
			VALUES(DITS.DATA_MONITOR_LOG_SEQ.nextval, 'DITS.NSM', 'DITS.DATA_MONITOR_TB', 2, TRIM(CAST(:OLD.DATA_ID AS CHAR(128))), 1);
	ELSE
		INSERT INTO DITS.DATA_MONITOR_LOG(SID, NSM, CLTB, OPT, CLID, INUSE)
			VALUES(DITS.DATA_MONITOR_LOG_SEQ.nextval, 'DITS.NSM', 'DITS.DATA_MONITOR_TB', 3, TRIM(CAST(:OLD.DATA_ID AS CHAR(128))), 1 );
		INSERT INTO DITS.DATA_MONITOR_LOG(SID, NSM, CLTB, OPT, CLID, INUSE)
			VALUES(DITS.DATA_MONITOR_LOG_SEQ.nextval, 'DITS.NSM', 'DITS.DATA_MONITOR_TB', 1, TRIM(CAST(:NEW.DATA_ID AS CHAR(128))), 1 );
	END IF;
END;

--UNDEPLOY TRIGGER UPDATE
DROP TRIGGER DITS.DATA_MONITOR_UTG


--DEPLOY TABLE
CREATE SEQUENCE DITS.DATA_MONITOR_LOG_SEQ INCREMENT BY 1 START WITH 0 NOMAXVALUE MINVALUE 0 NOCYCLE ORDER

--DEPLOY TABLE
CREATE TABLE DITS.DATA_MONITOR_LOG(
	--自动主键
	SID          NUMBER(20)		not null PRIMARY KEY,
	--名字空间
	NSM       VARCHAR(42)    	not null,
	--变更的表名
	CLTB       VARCHAR(68)    	not null,
	--变更的操作,0-删除;1-插入; 2-更新
	OPT      	NUMBER(8)			not null,
	--变更数据的主键内容
	CLID        VARCHAR(256)  not null,
	--是否可用,0-不可用;1-可用
	INUSE     NUMBER(4)			default 1,
	--操作时间
	OPTM     TIMESTAMP       default SYSTIMESTAMP
)

--UNDEPLOY TABLE
DROP TABLE DITS.DATA_MONITOR_LOG

--UNDEPLOY TABLE
DROP SEQUENCE DITS.DATA_MONITOR_LOG_SEQ
